package com.lovo.roomProject.student.dao.impl;

import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.ClaszDO;
import com.lovo.roomProject.pojo.CutPageBean;
import com.lovo.roomProject.pojo.RoomDO;
import com.lovo.roomProject.pojo.StudentDO;
import com.lovo.roomProject.student.dao.StudentDAO;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl extends BaseDAO implements StudentDAO {
    StudentDO studentDO = new StudentDO();

    //数据初始化
    @Override
    public List listStudent() {
        this.getConnection();
        String sql = "select * from t_student s left join t_class c on s.s_c_id=c.c_id  left join t_room r on s.s_r_id=r.r_id";

        List list = new ArrayList();

        try {
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();
            while (rs.next()) {
                StudentDO studentDO = new StudentDO();
                studentDO.setId(rs.getInt("s_id"));
                studentDO.setName(rs.getString("s_name"));
                studentDO.setGender(rs.getString("s_gender"));
                studentDO.setPhone(rs.getString("s_phone"));
                studentDO.setDate(LocalDate.parse(rs.getString("s_date")));
                studentDO.setHead(rs.getString("s_head"));

                ClaszDO clasz = new ClaszDO();
                clasz.setId(rs.getInt("c_id"));
                clasz.setName(rs.getString("c_name"));
                studentDO.setClasz(clasz);

                RoomDO room = new RoomDO();
                room.setId(rs.getInt("r_id"));
                room.setAddr(rs.getString("r_addr"));
                studentDO.setRoom(room);


                list.add(studentDO);


            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();

        } finally {
            close(con, statement, rs);
        }


        return list;

    }


    //查询学生ID
    @Override
    public StudentDO getById(int id) {


//        List list = new ArrayList();
        this.getConnection();
        String sql = " select * from t_student s " +
                " left join t_class c on s.s_c_id=c.c_id " +
                " left join t_room r on s.s_r_id=r.r_id" +
                " where s.s_id=?";

        try {

            statement = con.prepareStatement(sql);

            statement.setObject(1, id);

            rs = statement.executeQuery();
            if (rs.next()) {

                studentDO.setId(rs.getInt("s_id"));
                studentDO.setName(rs.getString("s_name"));
                studentDO.setGender(rs.getString("s_gender"));
                studentDO.setPhone(rs.getString("s_phone"));
                studentDO.setDate(LocalDate.parse(rs.getString("s_date")));
                studentDO.setHead(rs.getString("s_head"));

                ClaszDO clasz = new ClaszDO();
                clasz.setId(rs.getInt("c_id"));
                clasz.setName(rs.getString("c_name"));


                RoomDO room = new RoomDO();
                room.setId(rs.getInt("r_id"));
                room.setAddr(rs.getString("r_addr"));

                studentDO.setRoom(room);
                studentDO.setClasz(clasz);

//                list.add(studentDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return studentDO;
    }

    /**
     * 添加学生
     *
     * @param studentDO
     */
    @Override
    public void insert(StudentDO studentDO) {
        this.getConnection();
        String sql = "insert into  t_student(s_name,s_gender,s_phone,s_date,s_head,s_c_id,s_r_id) " +
                " VALUES(?,?,?,?,?,?,?)";

        try {
            statement = con.prepareStatement(sql);

            statement.setObject(1, studentDO.getName());
            statement.setObject(2, studentDO.getGender());
            statement.setObject(3, studentDO.getPhone());
            statement.setObject(4, studentDO.getDate());
            statement.setObject(5, studentDO.getHead());
            statement.setObject(6, studentDO.getClasz() != null ? studentDO.getClasz().getId() : null);
            statement.setObject(7, studentDO.getRoom() != null ? studentDO.getRoom().getId() : null);
            statement.executeUpdate();

        } catch (SQLException throwables) {

            throwables.printStackTrace();
        } finally {
            close(con, statement);
        }


    }

    /**
     * 学生退房
     *
     * @param id
     */
    @Override
    public void delete(int id) {
        this.getConnection();
        String sql = "delete from t_student where s_id=?";

        try {
            statement = con.prepareStatement(sql);

            statement.setObject(1, id);

            statement.executeUpdate();

        } catch (SQLException throwables) {

            throwables.printStackTrace();
        } finally {

            close(con, statement);
        }
    }


    /**
     * 学生换房
     *
     * @param studentDO
     */
    //修改
    @Override
    public void update(StudentDO studentDO) {
        this.getConnection();
        String sql = "update t_student set s_r_id=? where s_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, studentDO.getRoom().getId());
            statement.setObject(2, studentDO.getId());
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

        }
    }


    /**
     * 查找和分页
     *
     * @param current
     * @param name
     * @param className
     * @param roomAddr
     * @return
     */
    @Override
    public CutPageBean listStudentByPage(int current, String name, String className, String roomAddr) {
        this.getConnection();
        String selectSql = "select * ";
        String countSql = "select count(*) cnt ";
        String fromSql = "from t_student s " +
                " left join t_class c on s.s_c_id=c.c_id " +
                " left join t_room r on s.s_r_id=r.r_id ";
        String limitSql = " limit ?,? ";

        String dynamicSql = "where 1=1 ";
        if (name != null && !name.equals("")) {
            dynamicSql += "and s.s_name like '%" + name + "%' ";
        }
        if (className != null && !className.equals("")) {
            dynamicSql += "and c.c_name like '%" + className + "%' ";
        }

        if (roomAddr != null && !roomAddr.equals("")) {
            dynamicSql += "and r.r_addr like '%" + roomAddr + "%' ";
        }


        CutPageBean cutPageBean = new CutPageBean();
        List list = new ArrayList();

        try {
            statement = con.prepareStatement(selectSql + fromSql + dynamicSql + limitSql);
            statement.setObject(1, (current - 1) * CutPageBean.PAGESIZE);
            statement.setObject(2, CutPageBean.PAGESIZE);
            rs = statement.executeQuery();

            while (rs.next()) {
                StudentDO studentDO = new StudentDO();
                studentDO.setId(rs.getInt("s_id"));
                studentDO.setName(rs.getString("s_name"));
                studentDO.setGender(rs.getString("s_gender"));
                studentDO.setPhone(rs.getString("s_phone"));
                studentDO.setDate(LocalDate.parse(rs.getString("s_date")));
                studentDO.setHead(rs.getString("s_head"));

                ClaszDO clasz = new ClaszDO();
                clasz.setId(rs.getInt("c_id"));
                clasz.setName(rs.getString("c_name"));
                studentDO.setClasz(clasz);

                RoomDO room = new RoomDO();
                room.setId(rs.getInt("r_id"));
                room.setAddr(rs.getString("r_addr"));
                studentDO.setRoom(room);


                list.add(studentDO);

            }
            cutPageBean.setList(list);

            statement = con.prepareStatement(countSql + fromSql + dynamicSql);
            rs = statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }


        return cutPageBean;
    }

//    public static void main(String[] args) {
//        StudentDAO studentDAO = new StudentDAOImpl();
//        System.out.println(studentDAO.listStudentByPage(1));
//
//
//    }


}
